package proyecto.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import javax.sql.DataSource;

import proyecto.exception.DAOExcepcion;
import proyecto.modelo.Persona;


public class PersonaDAOImpl extends BaseDAO implements PersonaDAO {

	private DataSource dataSource;
	
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}	
	
	public Persona detalle_persona(int in_codPersona) throws DAOExcepcion {
		Persona vo = new Persona();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String query =  "select in_codND,vc_nombre,vc_apPaterno,vc_apMaterno,ts_fechaNacimiento," +
							"bo_sexo,vc_direccion,vc_departPersona,vc_provPersona," +
							"vc_distPersona,in_telFijoPersona,in_telCelularPersona,vc_emailPersona,vc_ocupacionPersona,vc_estadoCivil " +
							"from persona where in_codND = ? ";
			con = dataSource.getConnection();			
			stmt = con.prepareStatement(query);
			stmt.setInt(1, in_codPersona);
			rs = stmt.executeQuery();
			if (rs.next()) {
				vo.setIn_codND(rs.getInt("in_codND")); 
				vo.setVcNombre(rs.getString("vc_nombre")); 
				vo.setVc_apPaterno(rs.getString("vc_apPaterno"));
				vo.setVc_apMaterno(rs.getString("vc_apMaterno")); 
				vo.setTs_fechaNacimiento(rs.getDate("ts_fechaNacimiento"));				
				vo.setBoSexo(rs.getByte("bo_sexo"));
				vo.setVcDireccion(rs.getString("vc_direccion"));
				vo.setVc_departPersona(rs.getString("vc_departPersona"));
				vo.setVc_provPersona(rs.getString("vc_provPersona"));
				vo.setVc_distPersona(rs.getString("vc_distPersona"));				
				vo.setIn_telFijoPersona(rs.getInt("in_telFijoPersona"));
				vo.setIn_telCelularPersona(rs.getInt("in_telCelularPersona"));
				vo.setVc_emailPersona(rs.getString("vc_emailPersona"));
				vo.setVc_ocupacionPersona(rs.getString("vc_ocupacionPersona"));				
				vo.setVc_estadoCivil(rs.getString("vc_estadoCivil"));
				System.out.println("Registro: " + vo.getIn_codND() + 
				 		   "," + vo.getVcNombre() + 
				 		   "," + vo.getVc_apPaterno() + 
				 		   "," + vo.getVc_apMaterno());				
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return vo;
	} 	
	
	public Persona insertar(Persona vo) throws DAOExcepcion {
		String query = "insert into persona (In_codND,vc_nombre,vc_apPaterno,vc_apMaterno,bo_sexo,vc_direccion,vc_departPersona," +
				       "vc_provPersona,vc_distPersona,in_telFijoPersona,in_telCelularPersona,vc_emailPersona,vc_ocupacionPersona,vc_estadoCivil) " +
				       "values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = dataSource.getConnection();
			stmt = con.prepareStatement(query);
			stmt.setInt(1, vo.getIn_codND());			
			stmt.setString(2, vo.getVcNombre());
			stmt.setString(3, vo.getVc_apPaterno());
			stmt.setString(4, vo.getVc_apMaterno());
			stmt.setByte(5, vo.getBoSexo());
			stmt.setString(6, vo.getVcDireccion());
			stmt.setString(7, vo.getVc_departPersona());
			stmt.setString(8, vo.getVc_provPersona());
			stmt.setString(9, vo.getVc_distPersona());			
			//stmt.setString(8, vo.getDa_fechaInscripPersonaCP().toString());
			stmt.setInt(10, vo.getIn_telFijoPersona());
			stmt.setInt(11, vo.getIn_telCelularPersona());
			stmt.setString(12, vo.getVc_emailPersona());
			stmt.setString(13, vo.getVc_ocupacionPersona());
			stmt.setString(14, vo.getVc_estadoCivil());			
			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo insertar");
			} // Obtener el ultimo id 
			query = "SELECT LAST_INSERT_ID()";
			stmt = con.prepareStatement(query);
			rs = stmt.executeQuery();
			if (rs.next()) {
				vo.setIn_codND(rs.getInt(1));
			}

		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return vo;
	}
	
	public Persona actualizar(Persona vo) throws DAOExcepcion {
		
		String query =  "update persona set vc_nombre=?,vc_apPaterno=?,vc_apMaterno=?," +
						"bo_sexo=?,vc_direccion=?,vc_departPersona=?," +
						"vc_provPersona=?,vc_distPersona=?,in_telFijoPersona=?,in_telCelularPersona=?, " +
						"vc_emailPersona=?,vc_ocupacionPersona=?,vc_estadoCivil=? " +						
						"where In_codND=?";
		
		Connection con = null;
		PreparedStatement stmt = null;
		try {
			con = dataSource.getConnection();
			stmt = con.prepareStatement(query);			
			stmt.setString(1, vo.getVcNombre());
			stmt.setString(2, vo.getVc_apPaterno());
			stmt.setString(3, vo.getVc_apMaterno());
			stmt.setByte(4, vo.getBoSexo());
			stmt.setString(5, vo.getVcDireccion());
			stmt.setString(6, vo.getVc_departPersona());
			stmt.setString(7, vo.getVc_provPersona());
			stmt.setString(8, vo.getVc_distPersona());			
			//stmt.setString(8, vo.getDa_fechaInscripPersonaCP().toString());
			stmt.setInt(9, vo.getIn_telFijoPersona());
			stmt.setInt(10, vo.getIn_telCelularPersona());
			stmt.setString(11, vo.getVc_emailPersona());
			stmt.setString(12, vo.getVc_ocupacionPersona());
			stmt.setString(13, vo.getVc_estadoCivil());
			stmt.setInt(14, vo.getIn_codND());			
			
			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo actualizar");
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return vo;
	}	

	public boolean actualizarDatosIntranetCP(Persona vo) throws DAOExcepcion {
		
		boolean updOK = false;
		String query =  "update persona set in_telFijoPersona=?,in_telCelularPersona=? " +
						"where In_codND=?";
		
		Connection con = null;
		PreparedStatement stmt = null;
		try {
			con = dataSource.getConnection();
			stmt = con.prepareStatement(query);			
			stmt.setInt(1, vo.getIn_telFijoPersona());
			stmt.setInt(2, vo.getIn_telCelularPersona());			
			stmt.setInt(3, vo.getIn_codND());		
			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo actualizar");
			}
			else	updOK = true;
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return updOK;
	}	
	
}
